﻿// ***********************************************************
// 员工的数据访问类
// Creator:yehui  Date:2013-02-19
// ***********************************************************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VipSys.SystemEntity;
using System.Data;
using VipSys.Common.Library;
using System.Diagnostics;

namespace VipSys.DataLayer
{
    public class EmployeeAccess
    {
        private static EmployeeAccess m_Instance = null;
        /// <summary>
        /// 单实例
        /// </summary>
        public static EmployeeAccess Instance
        {
            get
            {
                if (m_Instance == null)
                    m_Instance = new EmployeeAccess();
                return m_Instance;
            }
        }

        public short Add(Employee employee)
        {
            string szFiled = string.Format("{0},{1},{2},{3},{4},{5}"
                , SystemData.EmployeeTable.EmpNo
                , SystemData.EmployeeTable.Name
                , SystemData.EmployeeTable.OrgName
                , SystemData.EmployeeTable.PassWord
                , SystemData.EmployeeTable.RoleType
                , SystemData.EmployeeTable.Tel);
            string szValue = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}'"
                , employee.EmpNo
                , employee.Name
                , employee.OrgName
                , employee.PassWord
                , employee.RoleType
                , employee.Tel);
            string szSql = string.Format("Insert Into {0}({1}) values({2})"
                , SystemData.DBTable.EMPLOYEE
                , szFiled
                , szValue);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short Edit(Employee employee)
        {
            string szFiledValue = string.Format("{0}='{1}',{2}='{3}',{4}='{5}',{6}='{7}',{8}='{9}',{10}='{11}'"
                , SystemData.EmployeeTable.EmpNo, employee.EmpNo
                , SystemData.EmployeeTable.Name, employee.Name
                , SystemData.EmployeeTable.OrgName, employee.OrgName
                , SystemData.EmployeeTable.RoleType, employee.RoleType
                , SystemData.EmployeeTable.Tel, employee.Tel
                , SystemData.EmployeeTable.PassWord, employee.PassWord
                );
            string szCondition = string.Format(" {0}={1}"
                , SystemData.EmployeeTable.ID, employee.ID);
            string szSql = string.Format("update {0} set {1} where {2}"
                , SystemData.DBTable.EMPLOYEE
                , szFiledValue
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short Del(Employee employee)
        {
            string szCondition = string.Format(" {0}={1}"
                , SystemData.EmployeeTable.ID, employee.ID);
            string szSql = string.Format("Delete from {0}  where {1}"
                , SystemData.DBTable.EMPLOYEE
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetEmployeePageList(Employee query, ref List<Employee> lstEmployee)
        {
            if (lstEmployee == null)
                lstEmployee = new List<Employee>();
            lstEmployee.Clear();
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("SELECT TOP {0} * FROM {1}"
                , query.Limit
                , SystemData.DBTable.EMPLOYEE
                );
            if (query.Start != "0")
            {

                sbSql.Append(" WHERE (ID <");
                sbSql.AppendFormat(" (SELECT min(ID) FROM (SELECT TOP {0} id  FROM {1}  ORDER BY ID Desc) AS T)"
                    , query.Start
                    , SystemData.DBTable.EMPLOYEE
                    );
                sbSql.Append(" )");
            }
            sbSql.Append(" ORDER BY ID Desc");
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(sbSql.ToString());
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Employee employee = new Employee();
                    employee.EmpNo = ds.Tables[0].Rows[i][SystemData.EmployeeTable.EmpNo].ToString();
                    employee.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.EmployeeTable.ID].ToString());
                    employee.Name = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Name].ToString();
                    employee.OrgName = ds.Tables[0].Rows[i][SystemData.EmployeeTable.OrgName].ToString();
                    employee.PassWord = ds.Tables[0].Rows[i][SystemData.EmployeeTable.PassWord].ToString();
                    employee.RoleType = ds.Tables[0].Rows[i][SystemData.EmployeeTable.RoleType].ToString();
                    employee.Tel = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Tel].ToString();
                    lstEmployee.Add(employee);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetTotalCount(Employee query, ref int totalCount)
        {
            string szCondition = string.Format("1=1");
            string szSql = string.Format("select count(*) from {0} where {1}"
                , SystemData.DBTable.EMPLOYEE
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                totalCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetEmployee(Employee query, ref Employee employee)
        {
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6}"
                , SystemData.EmployeeTable.EmpNo
                , SystemData.EmployeeTable.ID
                , SystemData.EmployeeTable.Name
                , SystemData.EmployeeTable.OrgName
                , SystemData.EmployeeTable.PassWord
                , SystemData.EmployeeTable.RoleType
                , SystemData.EmployeeTable.Tel);
            string szCondition = string.Format("1=1");

            if (query.ID != 0)
                szCondition = string.Format("{0} and {1}={2}"
                    , szCondition
                    , SystemData.EmployeeTable.ID, query.ID.ToString());
            if (!string.IsNullOrEmpty(query.Name))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.EmployeeTable.Name, query.Name.ToString());
            if (!string.IsNullOrEmpty(query.EmpNo))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.EmployeeTable.EmpNo, query.EmpNo.ToString());
            if (!string.IsNullOrEmpty(query.PassWord))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.EmployeeTable.PassWord, query.PassWord.ToString());

            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.EMPLOYEE
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                employee.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.EmployeeTable.ID].ToString());
                employee.Name = ds.Tables[0].Rows[0][SystemData.EmployeeTable.Name].ToString();
                employee.EmpNo = ds.Tables[0].Rows[0][SystemData.EmployeeTable.EmpNo].ToString();
                employee.OrgName = ds.Tables[0].Rows[0][SystemData.EmployeeTable.OrgName].ToString();
                employee.PassWord = ds.Tables[0].Rows[0][SystemData.EmployeeTable.PassWord].ToString();
                employee.RoleType = ds.Tables[0].Rows[0][SystemData.EmployeeTable.RoleType].ToString();
                employee.Tel = ds.Tables[0].Rows[0][SystemData.EmployeeTable.Tel].ToString();
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        /// <summary>
        /// 通过工作人员姓名查找工作人员信息
        /// </summary>
        /// <param name="query"></param>
        /// <param name="employee"></param>
        /// <returns></returns>
        public short GetEmployeeByName(string szName, ref Employee employee)
        {
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6}"
                , SystemData.EmployeeTable.EmpNo
                , SystemData.EmployeeTable.ID
                , SystemData.EmployeeTable.Name
                , SystemData.EmployeeTable.OrgName
                , SystemData.EmployeeTable.PassWord
                , SystemData.EmployeeTable.RoleType
                , SystemData.EmployeeTable.Tel);
            string szCondition = string.Format("1=1");


            if (string.IsNullOrEmpty(szName))
                return SystemData.ReturnValue.PARAM_ERROR;

                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.EmployeeTable.Name, szName);

            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.EMPLOYEE
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                employee.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.EmployeeTable.ID].ToString());
                employee.Name = ds.Tables[0].Rows[0][SystemData.EmployeeTable.Name].ToString();
                employee.EmpNo = ds.Tables[0].Rows[0][SystemData.EmployeeTable.EmpNo].ToString();
                employee.OrgName = ds.Tables[0].Rows[0][SystemData.EmployeeTable.OrgName].ToString();
                employee.PassWord = ds.Tables[0].Rows[0][SystemData.EmployeeTable.PassWord].ToString();
                employee.RoleType = ds.Tables[0].Rows[0][SystemData.EmployeeTable.RoleType].ToString();
                employee.Tel = ds.Tables[0].Rows[0][SystemData.EmployeeTable.Tel].ToString();
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetEmployeeList(Employee query, ref List<Employee> lstEmployee)
        {
            if (lstEmployee == null)
                lstEmployee = new List<Employee>();
            lstEmployee.Clear();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6}"
              , SystemData.EmployeeTable.EmpNo
              , SystemData.EmployeeTable.ID
              , SystemData.EmployeeTable.Name
              , SystemData.EmployeeTable.OrgName
              , SystemData.EmployeeTable.PassWord
              , SystemData.EmployeeTable.RoleType
              , SystemData.EmployeeTable.Tel);
            string szCondition = string.Format("1=1");
            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.EMPLOYEE
                , szCondition);

            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Employee employee = new Employee();
                    employee.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.EmployeeTable.ID].ToString());
                    employee.Name = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Name].ToString();
                    employee.EmpNo = ds.Tables[0].Rows[i][SystemData.EmployeeTable.EmpNo].ToString();
                    employee.OrgName = ds.Tables[0].Rows[i][SystemData.EmployeeTable.OrgName].ToString();
                    employee.PassWord = ds.Tables[0].Rows[i][SystemData.EmployeeTable.PassWord].ToString();
                    employee.RoleType = ds.Tables[0].Rows[i][SystemData.EmployeeTable.RoleType].ToString();
                    employee.Tel = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Tel].ToString();
                    lstEmployee.Add(employee);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        /// <summary>
        /// 取出所有业务员信息
        /// </summary>
        /// <param name="lstEmployee"></param>
        /// <returns></returns>
        public short GetEmployeeList( ref List<Employee> lstEmployee)
        {
            if (lstEmployee == null)
                lstEmployee = new List<Employee>();
            lstEmployee.Clear();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6}"
              , SystemData.EmployeeTable.EmpNo
              , SystemData.EmployeeTable.ID
              , SystemData.EmployeeTable.Name
              , SystemData.EmployeeTable.OrgName
              , SystemData.EmployeeTable.PassWord
              , SystemData.EmployeeTable.RoleType
              , SystemData.EmployeeTable.Tel);
            string szCondition = string.Format("1=1");
            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.EMPLOYEE
                , szCondition);

            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Employee employee = new Employee();
                    employee.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.EmployeeTable.ID].ToString());
                    employee.Name = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Name].ToString();
                    employee.EmpNo = ds.Tables[0].Rows[i][SystemData.EmployeeTable.EmpNo].ToString();
                    employee.OrgName = ds.Tables[0].Rows[i][SystemData.EmployeeTable.OrgName].ToString();
                    employee.PassWord = ds.Tables[0].Rows[i][SystemData.EmployeeTable.PassWord].ToString();
                    employee.RoleType = ds.Tables[0].Rows[i][SystemData.EmployeeTable.RoleType].ToString();
                    employee.Tel = ds.Tables[0].Rows[i][SystemData.EmployeeTable.Tel].ToString();
                    lstEmployee.Add(employee);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
    }
}
